/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package queenb.net.datacontrol;

import java.lang.reflect.Field;
import java.lang.reflect.InvocationTargetException;
import java.math.BigDecimal;
import java.sql.CallableStatement;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.sql.Time;
import java.sql.Timestamp;
import java.sql.Types;
import java.util.ArrayList;
import java.util.List;
import javax.faces.context.FacesContext;
//import oracle.jdbc.OracleCallableStatement;
import oracle.jdbc.OracleTypes;
import org.apache.log4j.Logger;
//import org.hibernate.Query;
import org.hibernate.SessionFactory;
import org.hibernate.Transaction;
import org.hibernate.classic.Session;
import queenb.net.annotation.Mapping;
import queenb.net.util.QueenbUtil;
import queenb.web.session.LoginSession;
import queenb.web.util.SessionProvider;
import java.sql.Types;
import java.util.logging.Level;
import queenb.net.util.HibernateUtil;

/**
 *
 * @author thanhct
 */
public class DataStatement<T> {

    private Logger logger = Logger.getLogger(DataStatement.class);
    private int sqlType;
    private float sqlTypeFloat;
    private int[] sqlTypes;
    private Param[] mapParam;
    private OutParam[] listOutParams;
    private int paramCount;
    private String procedureName;
    private final String QUERY_PREFIX = "call";
    private final String UPDATE_PREFIX = "?= call";
    private final String ZERO_VALUE = "0";

    public DataStatement(String procedureName, int sqlType, Param[] mapParam) {
        this.procedureName = procedureName;
        this.sqlType = sqlType;
        this.mapParam = mapParam;
        if (getMapParam() != null) {
            this.paramCount = mapParam.length;
        }
    }

    public DataStatement(String procedureName, float sqlType, Param[] mapParam) {
        this.procedureName = procedureName;
        this.sqlTypeFloat = sqlType;
        this.mapParam = mapParam;
        if (getMapParam() != null) {
            this.paramCount = mapParam.length;
        }
    }

    public DataStatement(String procedureName, int[] sqlTypes, Param[] mapParam) {
        this.procedureName = procedureName;
        this.sqlTypes = sqlTypes;
        this.mapParam = mapParam;
        if (getMapParam() != null) {
            this.paramCount = mapParam.length + (sqlTypes.length - 1);
        }
    }

    public DataStatement(String procedureName, Param[] mapParam) {
        this.procedureName = procedureName;
        this.mapParam = mapParam;
        if (getMapParam() != null) {
            this.paramCount = mapParam.length;
        }
    }

    public DataStatement(String procedureName, OutParam[] listOutParams, Param[] mapParam) {
        this.procedureName = procedureName;
        this.mapParam = mapParam;
        this.listOutParams = listOutParams;
        if (getMapParam() != null) {
            this.paramCount = mapParam.length;
        }
    }

    public String getStringQuery(String prefix, String parameters) {
        if (parameters.toString().equals("no?")) {
            return "{ " + prefix + " " + procedureName + " }";
        }
        return "{ " + prefix + " " + procedureName + "(" + parameters + ") }";
    }

    private String getParameters(int paramCount) {
        String reVal = "";
        for (int i = 1; i <= paramCount; i++) {
            if (i < paramCount) {
                reVal += "?,";
            }
            if (i == paramCount) {
                reVal += "?";
            }
        }
        return reVal;
    }

    private CallableStatement putParameter(CallableStatement clst) throws SQLException {
        int position = 1;//xac dinh vi tri cua tham so trong callabeStatement
        if (mapParam != null) {
            for (int i = 0; i < mapParam.length; i++) {
                Param param = mapParam[i];
                Object value = param.getValue();
                int type = param.getType();
                String valueStr;
                if (value != null) {
                    valueStr = value.toString();
                } else {
                    if (type == Types.INTEGER) {
                        valueStr = ZERO_VALUE;
                    } else {
                        valueStr = null;
                    }
                }
                switch (param.getType()) {
                    case Types.INTEGER:
                        clst.setInt(position, Integer.parseInt(valueStr));
                        position++;
                        break;
                    case Types.CHAR:
                        clst.setString(position, valueStr);
                        position++;
                        break;
                    case Types.VARCHAR:
                        clst.setString(position, valueStr);
                        position++;
                        break;
                    case Types.DOUBLE:
                        clst.setDouble(position, value == null ? null : new Double(valueStr));
                        position++;
                        break;
                    case Types.CLOB:
                        clst.setString(position, valueStr);
                        position++;
                        break;
                    case Types.BLOB:
                        clst.setBytes(position, (byte[]) value);
                        position++;
                        break;
                    case Types.LONGVARBINARY:
                        clst.setBytes(position, (byte[]) value);
                        position++;
                        break;
                    case Types.NULL:
                        clst.setNull(position, Types.NULL);
                        position++;
                        break;
                    case Types.DATE:
                        clst.setDate(position, (java.sql.Date) value);
                        position++;
                        break;
                    case Types.BIGINT:
                        clst.setBigDecimal(position, value == null ? null : new BigDecimal(valueStr));
                        position++;
                        break;
                    case Types.TIMESTAMP:
                        clst.setTimestamp(position, value == null ? null : (Timestamp) value);
                        position++;

                }
            }
        }
        return clst;
    }

    private CallableStatement putParameter4Function(CallableStatement clst) throws SQLException {
        int position = 2;//xac dinh vi tri cua tham so trong callabeStatement
        if (mapParam != null) {
            for (int i = 0; i < mapParam.length; i++) {
                Param param = mapParam[i];
                Object value = param.getValue();
                int type = param.getType();
                String valueStr;
                if (value != null) {
                    valueStr = value.toString();
                } else {
                    if (type == Types.INTEGER) {
                        valueStr = ZERO_VALUE;
                    } else {
                        valueStr = null;
                    }
                }
                switch (param.getType()) {
                    case Types.INTEGER:
                        clst.setInt(position, Integer.parseInt(valueStr));
                        position++;
                        break;
                    case Types.CHAR:
                        clst.setString(position, valueStr);
                        position++;
                        break;
                    case Types.VARCHAR:
                        clst.setString(position, valueStr);
                        position++;
                        break;
                    case Types.DOUBLE:
                        clst.setDouble(position, value == null ? null : new Double(valueStr));
                        position++;
                        break;
                    case Types.CLOB:
                        clst.setString(position, valueStr);
                        position++;
                        break;
                    case Types.BLOB:
                        clst.setBytes(position, (byte[]) value);
                        position++;
                        break;
                    case Types.LONGVARBINARY:
                        clst.setBytes(position, (byte[]) value);
                        byte[] test = (byte[]) value;
                        System.out.println("%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%Kich thuot= " + test.length);
                        position++;
                        break;
                    case Types.NULL:
                        clst.setNull(position, Types.NULL);
                        position++;
                        break;
                    case Types.DATE:
                        clst.setDate(position, (java.sql.Date) value);
                        position++;
                        break;
                    case Types.BIGINT:
                        clst.setBigDecimal(position, value == null ? null : new BigDecimal(valueStr));
                        position++;
                        break;
                    case Types.TIMESTAMP:
                        clst.setTimestamp(position, value == null ? null : (Timestamp) value);
                        position++;

                }
            }
        }
        return clst;
    }

    //Dung de truy van du lieu(thuong la cau SELECT trong Procedure)
    @SuppressWarnings("unchecked")
    public List<T> executeQuery(Class clazz) throws SQLException, InstantiationException, IllegalAccessException {
        Object idf = null;
        try {
            //Session ss = openSession();
            // Session ss = QueenbUtil.getSessionFactory().getCurrentSession();
            // Transaction tss = startTransaction(ss);
            //QueenbUtil.beginWriteTransaction();
            idf = HibernateUtil.createSession();
            Session ss = (Session) HibernateUtil.getSession();
            Transaction tx = ss.beginTransaction();
            String queryString = this.getStringQuery(QUERY_PREFIX, preparePlaceHolders(paramCount));
            System.out.println("####Goi ham=" + queryString);
            CallableStatement clst = ss.connection().prepareCall(queryString);
            clst = putParameter(clst);
            boolean r = clst.execute();

            List<T> lstEntity = new ArrayList<T>();
            ResultSet rs = clst.getResultSet();
            if (rs != null) {
                ResultSetMetaData rsMt = rs.getMetaData();
                while (rs.next()) {
                    int columnNameCount = rsMt.getColumnCount();
                    Object objClazz = clazz.newInstance();
                    Field[] fields = clazz.getDeclaredFields();
                    for (int j = 0; j < fields.length; j++) {
                        Field field = fields[j];
                        Mapping mapping = field.getAnnotation(Mapping.class);
                        mappingFields(mapping, columnNameCount, rsMt, field, objClazz, rs);
                    }
                    lstEntity.add((T) objClazz);
                }
            }
            // QueenbUtil.commitTransaction();
            //closeSession(ss);
            tx.commit();
            HibernateUtil.closeSession(idf);
            return lstEntity;
        } catch (Exception ex) {
            java.util.logging.Logger.getLogger(DataStatement.class.getName()).log(Level.SEVERE, null, ex);
            return null;
        }
//        finally {
//            try {
//                HibernateUtil.closeSession(idf);
//            } catch (Exception ex) {
//                java.util.logging.Logger.getLogger(DataStatement.class.getName()).log(Level.SEVERE, null, ex);
//            }
//        }
    }

    //Dung de thuc hien goi ham(Function) trong MySQL
    public int executeUpdate(Class<T> entityClass) throws SQLException {
        Object obj = executeUpdateReturnObject(entityClass);
        if (obj != null) {
            return Integer.parseInt(obj.toString());
        } else {
            return -1;
        }

    }

    public int executeUpdateNoOutParam(Class<T> entityClass) throws SQLException {
        Object obj = executeUpdateReturnObjectNoOutParam(entityClass);
        return Integer.parseInt(obj.toString());
    }

    public Object executeUpdateReturnObjectNoOutParam(Class<T> entityClass) throws SQLException {
        Object idf = null;
        try {

            //Session ss = openSession();
            //Session ss = QueenbUtil.getSessionFactory().getCurrentSession();
            // Transaction tss = startTransaction(ss);
            // QueenbUtil.beginWriteTransaction();
            idf = HibernateUtil.createSession();
            Session ss = (Session) HibernateUtil.getSession();
            Transaction tx = ss.beginTransaction();
            String queryString = this.getStringQuery(QUERY_PREFIX, preparePlaceHolders(paramCount));
            System.out.println("####Goi ham=" + queryString);
            CallableStatement clst = ss.connection().prepareCall(queryString);
            // clst.registerOutParameter(0, sqlType);
            clst = putParameter(clst);
            //clst.setString(procedureName, ZERO_VALUE);
            clst.executeQuery();
            // commitTransaction(tss);
            //  QueenbUtil.commitTransaction();
            tx.commit();
            HibernateUtil.closeSession(idf);
            //closeSession(ss);
            return sqlType;
        } catch (Exception ex) {
            java.util.logging.Logger.getLogger(DataStatement.class.getName()).log(Level.SEVERE, null, ex);
            return null;
        }
//        finally {
//            try {
//                HibernateUtil.closeSession(idf);
//            } catch (Exception ex) {
//                java.util.logging.Logger.getLogger(DataStatement.class.getName()).log(Level.SEVERE, null, ex);
//            }
//        }
    }

    public Object executeUpdateReturnObject(Class<T> entityClass) throws SQLException {
        Object idf = null;
        try {

            //Session ss = openSession();
            //  Session ss = QueenbUtil.getSessionFactory().getCurrentSession();
            // Transaction tss = startTransaction(ss);
            // QueenbUtil.beginWriteTransaction();
            idf = HibernateUtil.createSession();
            Session ss = (Session) HibernateUtil.getSession();
            Transaction tx = ss.beginTransaction();
            String queryString = this.getStringQuery(UPDATE_PREFIX, preparePlaceHolders(paramCount));
            System.out.println("####Goi ham=" + queryString);
            CallableStatement clst = ss.connection().prepareCall(queryString);
            clst.registerOutParameter(1, sqlType);
            clst = putParameter4Function(clst);
            clst.executeUpdate();
            //  commitTransaction(tss);
            // QueenbUtil.commitTransaction();
            //closeSession(ss);
            tx.commit();
            HibernateUtil.closeSession(idf);
            return filterReturn(sqlType, clst);
        } catch (Exception ex) {
            java.util.logging.Logger.getLogger(DataStatement.class.getName()).log(Level.SEVERE, null, ex);
            return null;
        }
//        finally {
//            try {
//                HibernateUtil.closeSession(idf);
//            } catch (Exception ex) {
//                java.util.logging.Logger.getLogger(DataStatement.class.getName()).log(Level.SEVERE, null, ex);
//            }
//        }
    }

    private Object filterReturn(int returnType, CallableStatement clst) throws SQLException {
        Object reVal = null;
        switch (returnType) {
            case Types.INTEGER:
                reVal = clst.getInt(1);
                break;
            case Types.CHAR:
                reVal = clst.getString(1);
                break;
            case Types.VARCHAR:
                reVal = clst.getString(1);
                break;
            case Types.CLOB:
                reVal = clst.getString(1);
                break;
            case Types.BLOB:
                reVal = clst.getBytes(1);
                break;
            case Types.LONGVARBINARY:
                reVal = clst.getBytes(1);
                break;
            case Types.DATE:
                reVal = clst.getDate(1);
                break;
            case Types.BIGINT:
                reVal = clst.getBigDecimal(1);
                break;
            case Types.DOUBLE:
                reVal = clst.getDouble(1);
                break;
            case Types.TIMESTAMP:
                reVal = clst.getTime(1);
        }
        return reVal;
    }

    public List executeUpdateMultiReturn(Class<T> entityClass) throws SQLException {
        List reVal = new ArrayList();
        //Session ss = openSession();
        Session ss = QueenbUtil.getSessionFactory().getCurrentSession();
        Transaction tss = startTransaction(ss);
        String query = this.getStringQuery(UPDATE_PREFIX, getParameters(paramCount - 1));
        CallableStatement clst = ss.connection().prepareCall("{" + query + "}");
        for (int i = 0; i < sqlTypes.length; i++) {
            clst.registerOutParameter(i + 1, sqlTypes[i]);
        }
        clst = putParameter(clst, sqlTypes.length + 1);
        reVal.add(clst.executeUpdate());
        commitTransaction(tss);
        //closeSession(ss);
        for (int i = 0; i < sqlTypes.length; i++) {
            int rePos = i + 1;
            switch (sqlTypes[i]) {
                case Types.INTEGER:
                    reVal.add(clst.getInt(rePos));
                    break;
                case Types.CHAR:
                    reVal.add(clst.getString(rePos));
                    break;
                case Types.VARCHAR:
                    reVal.add(clst.getString(rePos));
                    break;
                case Types.CLOB:
                    reVal.add(clst.getString(rePos));
                    break;
                case Types.BLOB:
                    reVal.add(clst.getBytes(rePos));
                    break;
                case Types.LONGVARBINARY:
                    reVal.add(clst.getBytes(rePos));
                    break;
                case Types.DATE:
                    reVal.add(clst.getDate(rePos));
                    break;
                case Types.BIGINT:
                    reVal.add(clst.getBigDecimal(rePos));
                    break;
                case Types.DOUBLE:
                    reVal.add(clst.getDouble(rePos));
                    break;
                case Types.TIMESTAMP:
                    reVal.add(clst.getTimestamp(rePos));
            }
        }
        return reVal;
    }

    public List executeUpdateMultiReturnNew(Class<T> entityClass) throws SQLException {
        List reVal = new ArrayList();
        //Session ss = openSession();
        Session ss = QueenbUtil.getSessionFactory().getCurrentSession();
        Transaction tss = startTransaction(ss);
        String queryString = this.getStringQuery(QUERY_PREFIX, preparePlaceHolders(paramCount));
        System.out.println("####Goi ham=" + queryString);
        CallableStatement clst = ss.connection().prepareCall(queryString);
        for (int i = 0; i < listOutParams.length; i++) {
            clst.registerOutParameter(listOutParams[i].getIndex(), listOutParams[i].getType());
        }
        clst = putParameter4ProcWithOut(clst, listOutParams);
        boolean r = clst.execute();
        for (int i = 0; i < listOutParams.length; i++) {
            switch (listOutParams[i].getType()) {
                case Types.INTEGER:
                    reVal.add(clst.getInt(listOutParams[i].getIndex()));
                    break;
                case Types.CHAR:
                    reVal.add(clst.getString(listOutParams[i].getIndex()));
                    break;
                case Types.VARCHAR:
                    reVal.add(clst.getString(listOutParams[i].getIndex()));
                    break;
                case Types.DATE:
                    reVal.add(clst.getDate(listOutParams[i].getIndex()));
                    break;
                case Types.BIGINT:
                    reVal.add(clst.getBigDecimal(listOutParams[i].getIndex()));
                    break;
                case Types.DOUBLE:
                    reVal.add(clst.getDouble(listOutParams[i].getIndex()));
                    break;
                case Types.TIMESTAMP:
                    reVal.add(clst.getTimestamp(listOutParams[i].getIndex()));
            }
        }
        commitTransaction(tss);
        //closeSession(ss);
        //ResultSet rs = clst.getResultSet();
        return reVal;
    }

    private CallableStatement putParameter4ProcWithOut(CallableStatement clst, OutParam[] listOutParam) throws SQLException {
        int position = 1;//xac dinh vi tri cua tham so trong callabeStatement
        for (int i = 0; i < mapParam.length - listOutParam.length; i++) {
            Param param = mapParam[i];
            Object value = param.getValue();
            int type = param.getType();
            String valueStr;
            if (value != null) {
                valueStr = value.toString();
            } else {
                if (type == Types.INTEGER) {
                    valueStr = ZERO_VALUE;
                } else {
                    valueStr = null;
                }
            }
            switch (param.getType()) {
                case Types.INTEGER:
                    clst.setInt(position, Integer.parseInt(valueStr));
                    position++;
                    break;
                case Types.CHAR:
                    clst.setString(position, valueStr);
                    position++;
                    break;
                case Types.VARCHAR:
                    clst.setString(position, valueStr);
                    position++;
                    break;
                case Types.DOUBLE:
                    clst.setDouble(position, value == null ? null : new Double(valueStr));
                    position++;
                    break;
                case Types.CLOB:
                    clst.setString(position, valueStr);
                    position++;
                    break;
                case Types.BLOB:
                    clst.setBytes(position, (byte[]) value);
                    position++;
                    break;
                case Types.LONGVARBINARY:
                    clst.setBytes(position, (byte[]) value);
                    position++;
                    break;
                case Types.NULL:
                    clst.setNull(position, Types.NULL);
                    position++;
                    break;
                case Types.DATE:
                    clst.setDate(position, (java.sql.Date) value);
                    position++;
                    break;
                case Types.BIGINT:
                    clst.setBigDecimal(position, value == null ? null : new BigDecimal(valueStr));
                    position++;
                    break;
                case Types.TIMESTAMP:
                    clst.setTimestamp(position, value == null ? null : (Timestamp) value);
                    position++;

            }
        }
        return clst;
    }

    private Transaction startTransaction(Session ss) {
        Transaction tss = ss.beginTransaction();
        return tss;
    }

    private void commitTransaction(Transaction transaction) {
        transaction.commit();
    }

    private Session openSession() {
        SessionFactory ssf;
        Session ss;

        //LoginSession loginss = (LoginSession) SessionProvider.getSession("loginSession", FacesContext.getCurrentInstance());
        ssf = QueenbUtil.getSessionFactory();
        ss = ssf.openSession();

        return ss;
    }

    private void closeSession(Session ss) {

//            ss.flush();
//            ss.connection().commit();
        ss.close();


    }

    private CallableStatement putParameter(CallableStatement clst, int returnCount) throws SQLException {

        for (int i = 0; i < mapParam.length; i++) {
            Param param = mapParam[i];
            Object value = param.getValue();
            int type = param.getType();
            String valueStr;
            if (value != null) {
                valueStr = value.toString();
            } else {
                if (type == Types.INTEGER) {
                    valueStr = ZERO_VALUE;
                } else {
                    valueStr = null;
                }
            }
            int posParam = i + returnCount;
            switch (param.getType()) {
                case Types.INTEGER:
                    clst.setInt(posParam, Integer.parseInt(valueStr));
                    break;
                case Types.CHAR:
                    clst.setString(posParam, valueStr);
                    break;
                case Types.VARCHAR:
                    clst.setString(posParam, valueStr);
                    break;
                case Types.CLOB:
                    clst.setString(posParam, valueStr);
                    break;
                case Types.BLOB:
                    clst.setBytes(posParam, (byte[]) value);
                    break;
                case Types.LONGVARBINARY:
                    clst.setBytes(posParam, (byte[]) value);
                    break;
                case Types.NULL:
                    clst.setNull(posParam, Types.NULL);
                    break;
                case Types.DATE:
                    clst.setDate(posParam, (java.sql.Date) value);
                    break;
                case Types.DOUBLE:
                    clst.setDouble(posParam, value == null ? null : new Double(valueStr));
                    break;
                case Types.BIGINT:
                    clst.setBigDecimal(posParam, value == null ? null : new BigDecimal(valueStr));
                    break;
                case Types.TIMESTAMP:
                    clst.setTimestamp(posParam, value == null ? null : (Timestamp) value);


            }
        }
        return clst;
    }

    private CallableStatement putParameterPro(CallableStatement clst) throws SQLException {
        int posParam = 2;
        for (int i = 1; i < mapParam.length; i++) {
            Param param = mapParam[i];
            Object value = param.getValue();
            int type = param.getType();
            String valueStr;
            if (value != null) {
                valueStr = value.toString();
            } else {
                if (type == Types.INTEGER) {
                    valueStr = ZERO_VALUE;
                } else {
                    valueStr = null;
                }

            }
            //  int posParam = i + returnCount;
            switch (param.getType()) {
                case Types.INTEGER:
                    clst.setInt(posParam, Integer.parseInt(valueStr));
                    posParam++;
                    break;
                case Types.CHAR:
                    clst.setString(posParam, valueStr);
                    posParam++;
                    break;
                case Types.VARCHAR:
                    clst.setString(posParam, valueStr);
                    posParam++;
                    break;
                case Types.CLOB:
                    clst.setString(posParam, valueStr);
                    posParam++;
                    break;
                case Types.BLOB:
                    clst.setBytes(posParam, (byte[]) value);
                    posParam++;
                    break;
                case Types.LONGVARBINARY:
                    clst.setBytes(posParam, (byte[]) value);
                    posParam++;
                    break;
                case Types.NULL:
                    clst.setNull(posParam, Types.NULL);
                    posParam++;
                    break;
                case Types.DATE:
                    clst.setDate(posParam, (java.sql.Date) value);
                    posParam++;
                    break;
                case Types.DOUBLE:
                    clst.setDouble(posParam, value == null ? null : new Double(valueStr));
                    posParam++;
                    break;
                case Types.BIGINT:
                    clst.setBigDecimal(posParam, value == null ? null : new BigDecimal(valueStr));
                    posParam++;
                    break;
                case Types.TIMESTAMP:
                    clst.setTimestamp(posParam, value == null ? null : (Timestamp) value);
                    posParam++;


            }
        }
        return clst;
    }

    private void mappingFields(Mapping mapping, int columnNameCount, ResultSetMetaData rsMt, Field field, Object objClazz, ResultSet rs) throws SQLException, IllegalArgumentException, IllegalAccessException {
        if (mapping != null) {
            String mappingName = mapping.name();
            for (int i = 0; i < columnNameCount; i++) {
                //String columnName = rsMt.getColumnName(i + 1);
                String columnName = rsMt.getColumnLabel(i + 1);
                if (mappingName.equalsIgnoreCase(columnName)) {
                    int typeMapping = mapping.typeMapping();
                    mappingType(typeMapping, field, objClazz, rs, columnName);
                    break;
                }
            }
        }
    }

    private void mappingType(int typeMapping, Field field, Object objClazz, ResultSet rs, String columnName) throws IllegalArgumentException, IllegalAccessException, SQLException {
        //String methodName = "set" + shiftStartChar(field.getName());
        String methodName = "set" + shiftStartChar(field.getName());
        try {
            Class[] types = new Class[1];
            switch (typeMapping) {
                case Types.BIGINT:
                    //                field.set(objClazz, rs.getBigDecimal(columnName));
                    types[0] = BigDecimal.class;
                    objClazz.getClass().getMethod(methodName, types).invoke(objClazz, rs.getBigDecimal(columnName));
                    break;
                case Types.INTEGER:
                    //                field.set(objClazz, rs.getInt(columnName));
                    types[0] = int.class;
                    objClazz.getClass().getMethod(methodName, types).invoke(objClazz, rs.getInt(columnName));
                    break;
//                case Types.NUMBER:
//                    //                field.set(objClazz, rs.getInt(columnName));
//                    types[0] = int.class;
//                    objClazz.getClass().getMethod(methodName, types).invoke(objClazz, rs.getInt(columnName));
//                    break;
                case Types.DOUBLE:
                    //                field.set(objClazz, rs.getInt(columnName));
                    types[0] = Double.class;
                    objClazz.getClass().getMethod(methodName, types).invoke(objClazz, rs.getDouble(columnName));
                    break;
                case Types.CHAR:
                    //                field.set(objClazz, rs.getString(columnName));
                    types[0] = String.class;
                    objClazz.getClass().getMethod(methodName, types).invoke(objClazz, rs.getString(columnName));
                    break;
                case Types.VARCHAR:
                    //                field.set(objClazz, rs.getString(columnName));
                    types[0] = String.class;
                    objClazz.getClass().getMethod(methodName, types).invoke(objClazz, rs.getString(columnName));
                    break;
                case Types.CLOB:
                    //                field.set(objClazz, rs.getString(columnName));
                    types[0] = String.class;
                  
                    objClazz.getClass().getMethod(methodName, types).invoke(objClazz, rs.getString(columnName));
                    break;
                case Types.BLOB:
                    //                field.set(objClazz, rs.getString(columnName));
                    types[0] = Byte[].class;
                    if(rs.getBytes(columnName)!=null){
                        field.set(objClazz, rs.getBytes(columnName));
                    }
                      
                    //System.out.print("--nc144----" + rs.getBytes(columnName));
                    objClazz.getClass().getMethod(methodName, types).invoke(objClazz, rs.getBytes(columnName));

                    break;
                case Types.LONGVARBINARY:
                    //                field.set(objClazz, rs.getString(columnName));
                    types[0] = Byte[].class;
                    objClazz.getClass().getMethod(methodName, types).invoke(objClazz, rs.getBytes(columnName));
                    break;
                case Types.DATE:
                    //                field.set(objClazz, rs.getDate(columnName));
                    types[0] = java.sql.Date.class;
                    objClazz.getClass().getMethod(methodName, types).invoke(objClazz, rs.getDate(columnName));
                    break;
                case Types.TIMESTAMP:
                    //                field.set(objClazz, rs.getTimestamp(columnName));
                    types[0] = Timestamp.class;
                    objClazz.getClass().getMethod(methodName, types).invoke(objClazz, rs.getTimestamp(columnName));
                    break;
                case Types.TIME:
                    //                field.set(objClazz, rs.getTime(columnName));
                    types[0] = Time.class;
                    objClazz.getClass().getMethod(methodName, types).invoke(objClazz, rs.getTime(columnName));
                    break;
                case Types.BOOLEAN:
                    //                field.set(objClazz, rs.getTime(columnName));
                    types[0] = Boolean.class;
                    objClazz.getClass().getMethod(methodName, types).invoke(objClazz, rs.getBoolean(columnName));
                    break;
                case Types.OTHER:
                    //                field.set(objClazz, rs.getTime(columnName));
                    types[0] = Object.class;
                    objClazz.getClass().getMethod(methodName, types).invoke(objClazz, rs.getObject(columnName));
                    break;
                default:
                    logger.debug("Invalid type mapping:" + typeMapping);
            }
        } catch (InvocationTargetException ex) {
            logger.debug("InvocationTargetException:" + ex.getMessage());
           // System.out.println("---mapptype11------------" + ex.getMessage());
        } catch (NoSuchMethodException ex) {
            logger.debug("NoSuchMethodException:" + ex.getMessage() + " with class invoked:" + methodName);
           // System.out.println("---mapptype2------------" + ex.getMessage());
        } catch (SecurityException ex) {
            logger.debug("SecurityException:" + ex.getMessage());
            //System.out.println("---mapptype33------------" + ex.getMessage());
        }
    }

    private String shiftStartChar(String text) {
        String reVal = text;
        if (text != null && !text.equals("")) {
            reVal = text.substring(0, 1).toUpperCase() + text.substring(1);
        }
        return reVal;
    }

    /**
     * @return the sqlType
     */
    public int getSqlType() {
        return sqlType;


    }

    /**
     * @param sqlType the sqlType to set
     */
    public void setSqlType(int sqlType) {
        this.sqlType = sqlType;


    }

    /**
     * @return the mapParam
     */
    public Param[] getMapParam() {
        return mapParam;


    }

    /**
     * @param mapParam the mapParam to set
     */
    public void setMapParam(Param[] mapParam) {
        this.mapParam = mapParam;


    }

    /**
     * @return the paramCount
     */
    public int getParamCount() {
        return paramCount;


    }

    /**
     * @param paramCount the paramCount to set
     */
    public void setParamCount(int paramCount) {
        this.paramCount = paramCount;


    }

    /**
     * @return the procedureName
     */
    public String getProcedureName() {
        return procedureName;


    }

    /**
     * @param procedureName the procedureName to set
     */
    public void setProcedureName(String procedureName) {
        this.procedureName = procedureName;


    }

    public static void main(String arg[]) {
//        System.out.println("start with:"+DataStatement.shiftStartChar("caoThacnh tgu sai"));
//        Calendar cl = Calendar.getInstance();
//        cl.add(Calendar.MONTH, 1);
//        Date endDate = new Date(cl.getTimeInMillis());
//        DataStatement sQLData = new DataStatement("Stdc_Tools.UpdateTabC150", oracle.jdbc.OracleTypes.INTEGER, new Param[]{
//                    new Param(OracleTypes.INTEGER, 300000588),
//                    new Param(OracleTypes.DATE, cl.getTime()),
//                    new Param(OracleTypes.DATE, endDate),
//                    new Param(OracleTypes.CHAR, "CTT")
//                });
//
//        String parameters = sQLData.getParameters(sQLData.paramCount - 1);
//        System.out.println(sQLData.getStringQuery("? = Call", parameters));
    }

    /**
     * @return the sqlTypeFloat
     */
    public float getSqlTypeFloat() {
        return sqlTypeFloat;
    }

    /**
     * @param sqlTypeFloat the sqlTypeFloat to set
     */
    public void setSqlTypeFloat(float sqlTypeFloat) {
        this.sqlTypeFloat = sqlTypeFloat;
    }

    public static String preparePlaceHolders(int length) {
        if (length == 0) {
            return "no?";
        }
        StringBuilder builder = new StringBuilder(length * 2 - 1);
        for (int i = 0; i < length; i++) {
            if (i > 0) {
                builder.append(',');
            }
            builder.append('?');
        }
        return builder.toString();
    }

    /**
     * @return the listOutParams
     */
    public OutParam[] getListOutParams() {
        return listOutParams;
    }

    /**
     * @param listOutParams the listOutParams to set
     */
    public void setListOutParams(OutParam[] listOutParams) {
        this.listOutParams = listOutParams;
    }
}
